package edu.gatech.TNE.wonderLab7000.M9;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.pool.OracleDataSource;

import edu.gatech.TNE.wonderLab7000.util.WonderLogger;

public class ADEmployee 
{
	private String query;
    private Connection conn;
    private ResultSet result;
    public WonderLogger logger = new WonderLogger("Mine");
    
 
    private String[][] allUsers;
    private int  i, j, userLength;
    
    public Connection connect() throws SQLException {
        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:oracle:thin:ops$mdye3/wonderLab@acme.gatech.edu:1525:public");
        conn = ods.getConnection();
        return conn;
    }
    
    public boolean createLeague(String name, Sport sport) throws SQLException
    {
    	conn = connect();
		query = "SELECT * FROM LEAGUES WHERE LEAGUENAME ='" + name + "'";
    	PreparedStatement statement = conn.prepareStatement(query);
    	
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            
			
		} 
		
		if (!result.next()) {
			conn = connect();
            query = "INSERT INTO LEAGUES (LEAGUENAME,SPORT) VALUES ('"+name+"', '" +sport.getName()+"')";
        	statement = conn.prepareStatement(query);
        	result = statement.executeQuery();
            statement.close();
            result.close();

            query = "CREATE TABLE " + name + " (TEAMNAME VARCHAR(100), CAPACITY VARCHAR(100), CAPTAIN VARCHAR(100), NUMPLAYERS VARCHAR(100), PLAYERS VARCHAR(100))";
        	statement = conn.prepareStatement(query);
        	result = statement.executeQuery();
        	
        	
    		return true;
        }
		else {
            conn.close();
            statement.close();
            result.close();
            return false;
		}
    }
    public boolean dropTable(String name) throws SQLException
    {
    	conn = connect();
		query = "DROP TABLE "+ name;
    	PreparedStatement statement = conn.prepareStatement(query);
    	
        try {
			result = statement.executeQuery();
			return true;
		} catch (SQLException e) {
            
			return false;
		} 
    }
    public boolean deleteFrom(String name) throws SQLException
    {
    	conn = connect();
		query = "DELETE FROM LEAGUES WHERE LEAGUENAME = '"+ name +"'";
    	PreparedStatement statement = conn.prepareStatement(query);
    	
        try {
			result = statement.executeQuery();
			return true;
		} catch (SQLException e) {
            
			return false;
		} 
    }
public String[][] getLeagues() throws SQLException {
		
		connect();
		query = "SELECT * FROM LEAGUES";
    	PreparedStatement statement = conn.prepareStatement(query);
    	allUsers = new String[100][100];
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            
			System.out.println(e.toString());
		} 
		
		userLength = 0;
		while (result.next()) {
			userLength++;
		}
		
		allUsers = new String[userLength][2];
		statement = conn.prepareStatement(query);
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            
			
		} 
		
		i = 0;
		while (result.next()) {
			j = 0;
			allUsers[i][j] = result.getString("LEAGUENAME");
			j++;
			
			i++;
		}
		
        conn.close();
        statement.close();
        result.close();
		return allUsers;
		
	}
}
